package com.uwang.library.dao.impl;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.uwang.library.dao.BtnDao;
import com.uwang.library.model.BtnModel;
import com.uwang.library.model.StatisticalDataModel;
import com.uwang.library.util.DruidUtil;

public class BtnDaoImpl implements BtnDao {
	
//	public static final String DB_DRIVER = "com.mysql.jdbc.Driver";
//	public static final String DB_URL = "jdbc:mysql://localhost:3306/tushuguan_demo";
//	public static final String DB_USER = "root";
//	public static final String DB_PASSWORD = "101023";
	PreparedStatement preparedStatement;
	ResultSet resultSet = null;
	

	@Override
	public BtnModel findAllMenu(int i) {
		BtnModel btnModel = new BtnModel();
//		Connection connection = null;
		try {
			Connection connection= DruidUtil.getConnection();
//			String sql = "delete from member where id=?";
//			String sql = "select * from shuji WHERE shuji.id=?";
			String sql = "select * from borrowing brg  WHERE book_id=? and brg.status != 0";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, i);
			resultSet = preparedStatement.executeQuery();
//			preparedStatement.executeUpdate();
			while (resultSet.next()) {
				
				btnModel.setId(resultSet.getInt("id"));
				btnModel.setName(resultSet.getString("user_id"));
				btnModel.setTerm(resultSet.getTimestamp("term"));
				} 
			}catch(SQLException e) {
				e.printStackTrace();
			}
		
		return btnModel;
		
	}

	@Override
	public void add(String userPhone,int bookId, Long time) {
		Connection connection= DruidUtil.getConnection();
		String sql = "insert into borrowing (user_id,book_id,borrowing_time,term,status) values((SELECT m.id FROM member m WHERE m.user_phone=?),?,NOW(),?,DEFAULT)";
		try {
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, userPhone);
			preparedStatement.setInt(2, bookId);
			preparedStatement.setTimestamp(3, new Timestamp(time));
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	
	
	
	//数据统计查询
	@Override
	public StatisticalDataModel AllData() {
		StatisticalDataModel statisticalDataModel = new StatisticalDataModel();
		try {
			Connection connection= DruidUtil.getConnection();
//			String sql = "delete from member where id=?";
//			String sql = "select * from shuji WHERE shuji.id=?";
			String sql = "select bk.booksNumber,brw.borrNumber,mb.memberNumber,brws.yuqiNumber from \r\n" + 
					"	(select count(*) booksNumber from books) bk,\r\n" + 
					"	(select count(*) borrNumber from borrowing) brw,\r\n" + 
					"	(select count(*) memberNumber from member) mb,\r\n" + 
					"	(select count(*) yuqiNumber from borrowing where term > borrowing_time and `status` != 0) brws;";
			preparedStatement = connection.prepareStatement(sql);
			resultSet = preparedStatement.executeQuery();
//			preparedStatement.executeUpdate();
			while (resultSet.next()) {
				statisticalDataModel.setBookNum(resultSet.getInt("booksNumber"));
				statisticalDataModel.setUserNum(resultSet.getInt("memberNumber"));
				statisticalDataModel.setBorrowNum(resultSet.getInt("borrNumber"));
				statisticalDataModel.setOverdueNum(resultSet.getInt("yuqiNumber"));
				System.out.println(statisticalDataModel.getBookNum());
				} 
			
			}catch(SQLException e) {
				e.printStackTrace();
			}
			return statisticalDataModel;
		
	}

	@Override
	public void bookReturn(int borrowId) {
		Connection connection = DruidUtil.getConnection();
		PreparedStatement PreparedStatement;
		String sql = "UPDATE borrowing set status=0 WHERE book_id=?";
		try {
			PreparedStatement= connection.prepareStatement(sql);
			PreparedStatement.setInt(1, borrowId);
			PreparedStatement.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}

	@Override
	public int queryId(int bookId) {
		int bookNum = 0;
		try {
			Connection connection= DruidUtil.getConnection();
//			String sql = "delete from member where id=?";
//			String sql = "select * from shuji WHERE shuji.id=?";
			String sql = "select count(*) num from books WHERE id=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, bookId);
			resultSet = preparedStatement.executeQuery();
			
			
			while (resultSet.next()) {
				bookNum = resultSet.getInt("num");
				} 
			}catch(SQLException e) {
				e.printStackTrace();
			}
		
		return bookNum;
	}

	@Override
	public int queryPhone(String userPhone) {
		int queryPhone = 0;
		try {
			Connection connection= DruidUtil.getConnection();
			String sql = "select count(*) num from member WHERE user_phone=?";
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setString(1, userPhone);
			resultSet = preparedStatement.executeQuery();
			
			
			while (resultSet.next()) {
				queryPhone = resultSet.getInt("num");
				} 
			}catch(SQLException e) {
				e.printStackTrace();
			}
		
		return queryPhone;
	}

}